﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;


using SMSSnew.DATA;
using SMSSnew.DATA.CLASSES;
using System.Diagnostics;
using Telerik.Web.UI.Calendar;
using System.IO;
using System.Text;

namespace SMSSnew.UI.subTeacher
{

    
    public partial class allocateSubTeachers : System.Web.UI.Page
    {

        string replacingteacherID;
        string slotID;
       
     

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)

            {
                //when the page loads the date picker loads the current date
                DateTime currentDate = new DateTime();
                currentDate = DateTime.Now;
                datepickernew.SelectedDate = currentDate;
               
            }

        }



       

       
        


        protected void AssignteachersBtn_Click(object sender, EventArgs e)
        {
            assignteachers();//call method
        }


        public void assignteachers()

        {
            substituteDA mysub2;

            try
            {
                mysub2 = new substituteDA();
                string dayofweek = System.DateTime.Now.DayOfWeek.ToString();
                DataTable dataTableAbsentTeachersTeacherIDandSlots = new DataTable();
                dataTableAbsentTeachersTeacherIDandSlots = mysub2.dataTableAbsentTeachersTeacherIDandSlots(dayofweek);//obtain absent teachers teacher_id and time slots from db
                
                DataTable noOf_Working_Slots_Of_Present_T = new DataTable();
                



                DataTable dataTablefoundsimilarslot = new DataTable();
                //create a column for named "teacher id"  and "slot" for the "dataTablefoundsimilarslot" datatable
                dataTablefoundsimilarslot.Columns.Add("teacher id");
                dataTablefoundsimilarslot.Columns.Add("slot");

                DataTable dataTablefirstAbsentTeacherRow = new DataTable();
                //create a column for named "teacher id"  and "slot" for the "dataTablefirstAbsentTeacherRow" datatable
                dataTablefirstAbsentTeacherRow.Columns.Add("teacher id");
                dataTablefirstAbsentTeacherRow.Columns.Add("slot");

                DataTable dataTableNo_OF_slots = new DataTable();
                dataTableNo_OF_slots.Columns.Add("Teacher ID");
                dataTableNo_OF_slots.Columns.Add("noofslots");



                
                DataTable dataTablePresentTeachersTeacherIDandSlots = new DataTable();

                dataTablePresentTeachersTeacherIDandSlots = mysub2.getPresentFreeTeachers();

                //find number of free slots of each teacher (T1 has 2 free slots likewise) relevant to  a particular day
                DataTable no_OF_Free_Periods = new DataTable();
                no_OF_Free_Periods.Columns.Add("teacherid");
                no_OF_Free_Periods.Columns.Add("noOfPeriods");
                var query = from row in dataTablePresentTeachersTeacherIDandSlots.AsEnumerable()
                            group row by row.Field<string>("teacher_id") into slots
                            orderby slots.Key
                            select new
                            {
                                Name = slots.Key,
                                CountOfTeachers = slots.Count()
                            };
                foreach (var teacherid in query)
                {
                    
                    object[] noOfTeachers = { teacherid.Name, teacherid.CountOfTeachers };

                    no_OF_Free_Periods.Rows.Add(noOfTeachers);//store counted value in " no_OF_Free_Periods" datatable
                }



                for (int j = 0; j < dataTableAbsentTeachersTeacherIDandSlots.Rows.Count; j++)//loop through all the absent teachers
                {
                    for (int k = 0; k < dataTablePresentTeachersTeacherIDandSlots.Rows.Count; k++)//loop through all the present teachers
                    {

                        int slotofabsentteacher = Convert.ToInt32(dataTableAbsentTeachersTeacherIDandSlots.Rows[j][1]);//select first slot of absent teacher
                        int slotofpresentteacher = Convert.ToInt32(dataTablePresentTeachersTeacherIDandSlots.Rows[k][1]);//select slot of present teacher
                        if (slotofabsentteacher == slotofpresentteacher)//check whether present teacher and absent teacher slots are equal
                        {
                            object[] o = { dataTablePresentTeachersTeacherIDandSlots.Rows[k][0], dataTablePresentTeachersTeacherIDandSlots.Rows[k][1] };//if present teacher and absent teacher slots are equal put present teachers' relevant teacher_id and slot_id into an array
                            dataTablefoundsimilarslot.Rows.Add(o);//add rows of above teacher id and slot to the "dataTablefoundsimilarslot" datatable
                        }


                    }

                    if (dataTablefoundsimilarslot.Rows.Count != 0)//if no of rows in the "dataTablefoundsimilarslot" datatable are not equal to 0
                    {
                        if (dataTablefoundsimilarslot.Rows.Count == 1)//if there is only one teacher who has a free slot that can be assigned   
                        {
                            string absentteacherID = dataTableAbsentTeachersTeacherIDandSlots.Rows[j][0].ToString();
                            string slotID = dataTableAbsentTeachersTeacherIDandSlots.Rows[j][1].ToString();
                            
                            DateTime.Now.ToString("yyyy-MM-dd");
                            string dateAsString = DateTime.Now.ToString("yyyy-MM-dd");
                            string replacingteacherID = dataTablefoundsimilarslot.Rows[0][0].ToString();
                            mysub2.insertassignedteachers(absentteacherID, slotID, dateAsString, replacingteacherID);//insert these values to db


                            for (int y = 0; y < dataTablePresentTeachersTeacherIDandSlots.Rows.Count; y++)//after inserting value have to remove that free slot and teacher id from present teacher free slots datatable
                            {

                                string replacing_teacher_ID = dataTablePresentTeachersTeacherIDandSlots.Rows[y][0].ToString();
                                string slot_ID = dataTablePresentTeachersTeacherIDandSlots.Rows[y][1].ToString();
                                if (replacingteacherID == replacing_teacher_ID && slotID == slot_ID)//if inserted teacher id ,slot value equals to present teachers free teachers teacher id,slot delete that row from present teacher free slot datatable
                                {

                                    dataTablePresentTeachersTeacherIDandSlots.Rows[y].Delete();//delete the row
                                    dataTablePresentTeachersTeacherIDandSlots.AcceptChanges();
                                }
                            }
                            dataTablefoundsimilarslot.Rows.Clear();//delete value put into "dataTablefoundsimilarslot" dt otherwise when counting its rows next time counting value will added previous one also.

                        }
                        if (dataTablefoundsimilarslot.Rows.Count > 1)//if no of rows in the "dataTablefoundsimilarslot" datatable are equal to 0
                        {
                            //create "dataTablefirstAbsentTeacherRow" dt and add row of selcted absent teacher id and slot 
                            object[] absentTdatarow = { dataTableAbsentTeachersTeacherIDandSlots.Rows[j][0], dataTableAbsentTeachersTeacherIDandSlots.Rows[j][1] };
                            dataTablefirstAbsentTeacherRow.Rows.Add(absentTdatarow);




                            //if there are more than one teacher free at the same time slot then check absent T & present T subjects
                            for (int u = 0; u < dataTablefirstAbsentTeacherRow.Rows.Count; u++)
                            {
                                for (int e = 0; e < dataTablefoundsimilarslot.Rows.Count; e++)
                                {
                                    string teacherIDAbsentT = dataTablefirstAbsentTeacherRow.Rows[0][0].ToString();
                                    DataTable dataTablegetAbsentTeacherSubject_typeID = new DataTable();
                                    dataTablegetAbsentTeacherSubject_typeID = mysub2.getsubject_type_ID_AbsentT(teacherIDAbsentT);//send the teacher id of absent teacher to db and select his/her subject

                                    string teacherIDPresentT = dataTablefoundsimilarslot.Rows[e][0].ToString();
                                    DataTable dataTablegetPresentTeacherSubject_typeID = new DataTable();
                                    dataTablegetPresentTeacherSubject_typeID = mysub2.getsubject_type_ID_PresentT(teacherIDPresentT);//send the teacher id of present teacher to db and select his/her subject

                                    DataTable dataTableallvaluesin_sub_T_all_Table = new DataTable();
                                    dataTableallvaluesin_sub_T_all_Table = mysub2.getallvaluesofS_T_A_table();



                                    if (dataTablegetAbsentTeacherSubject_typeID == dataTablegetPresentTeacherSubject_typeID)
                                    {
                                        for (int q = 0; q < dataTableallvaluesin_sub_T_all_Table.Rows.Count; q++)
                                        {
                                            string teacherID_in_db_T = dataTableallvaluesin_sub_T_all_Table.Rows[q][0].ToString();
                                            string slot_in_db_T = dataTableallvaluesin_sub_T_all_Table.Rows[q][1].ToString();
                                            string teacherID_in_dataTablefoundsimilarslot = dataTablefoundsimilarslot.Rows[q][0].ToString();
                                            string slot_in_dataTablefoundsimilarslot = dataTablefoundsimilarslot.Rows[q][1].ToString();

                                            if (teacherID_in_db_T == teacherID_in_dataTablefoundsimilarslot && slot_in_db_T != slot_in_dataTablefoundsimilarslot)
                                            {
                                                string absentteacherID = dataTablefirstAbsentTeacherRow.Rows[0][0].ToString();
                                                string slotID = dataTablefirstAbsentTeacherRow.Rows[0][1].ToString();
                                                
                                                DateTime.Now.ToString("yyyy-MM-dd");
                                                string dateAsString = DateTime.Now.ToString("yyyy-MM-dd");
                                                string replacingteacherID = dataTablefoundsimilarslot.Rows[0][0].ToString();
                                                mysub2.insertassignedteachers(absentteacherID, slotID, dateAsString, replacingteacherID);
                                            }
                                            //==========================add delete =======================================//
                                        }

                                    }

                                }
                                //if number of free slots are greater than one and if subects are also not eqaul then loop to this one.Here it checks number of free periods a teacher has for a day.
                                if (dataTablefoundsimilarslot.Rows.Count > 1)
                                {

                                    for (int w = 0; w < no_OF_Free_Periods.Rows.Count; w++)
                                    {
                                        for (int z = 0; z < dataTablefoundsimilarslot.Rows.Count; z++)
                                        {
                                            string teacherID = dataTablefoundsimilarslot.Rows[z][0].ToString();
                                            string teacher_ID = no_OF_Free_Periods.Rows[w][0].ToString();
                                            if (teacherID == teacher_ID)
                                            {
                                                // noOf_Working_Slots_Of_Present_T = mysub2.noOfWorkingSlotsOfPresentTeachers(teacherID, dayofweek);//get the number of working slots of teachers who has similar free slot to absent teacher
                                                object[] no_of_slots = { teacherID, no_OF_Free_Periods.Rows[w][1].ToString() };//add teacher id and no of working slots to "dataTableNo_OF_slots" dt
                                                //dataTableNo_OF_slots.Rows.Add(teacherID);
                                                dataTableNo_OF_slots.Rows.Add(no_of_slots);
                                            }
                                        }
                                    }
                                    noOf_Working_Slots_Of_Present_T.Rows.Clear();//clear all the values 

                                    for (int h = 0; h < dataTableNo_OF_slots.Rows.Count; h++)
                                    {
                                        int maxLevel = Convert.ToInt32(dataTableNo_OF_slots.Select("noofslots=max(noofslots)")[0][1]);//find the minimun value from 'number of slots' datatable

                                        //-----------here replacing_teacher_id can be any ,from more than one same no of slots(T25 18,T55 18)-----//

                                        for (int i = 0; i < dataTableNo_OF_slots.Rows.Count; i++)
                                        {
                                            if (maxLevel == Convert.ToInt32(dataTableNo_OF_slots.Rows[i][1]))//check whether min value equal to which "dataTableNo_OF_slots" dt min value
                                            {
                                                //if it is equal insert to db
                                                string absentteacherID = dataTableAbsentTeachersTeacherIDandSlots.Rows[j][0].ToString();
                                                string slotID = dataTableAbsentTeachersTeacherIDandSlots.Rows[j][1].ToString();
                                                string replacingteacherID = dataTableNo_OF_slots.Rows[i][0].ToString();
                                                DateTime.Now.ToString("yyyy-MM-dd");
                                                string dateAsString = DateTime.Now.ToString("yyyy-MM-dd");
                                                mysub2.insertassignedteachers(absentteacherID, slotID, dateAsString, replacingteacherID);
                                            }
                                            if (maxLevel == Convert.ToInt32(dataTableNo_OF_slots.Rows[i][1]))//delete the inserted value from dt present free teachers
                                            {
                                                for (int y = 0; y < dataTablePresentTeachersTeacherIDandSlots.Rows.Count; y++)
                                                {
                                                    string slotID = dataTableAbsentTeachersTeacherIDandSlots.Rows[j][1].ToString();
                                                    string replacingteacherID = dataTableNo_OF_slots.Rows[i][0].ToString();
                                                    string replacing_teacher_ID = dataTablePresentTeachersTeacherIDandSlots.Rows[y][0].ToString();
                                                    string slot_id = dataTablePresentTeachersTeacherIDandSlots.Rows[y][1].ToString();
                                                    if (replacingteacherID == replacing_teacher_ID && slotID == slot_id)
                                                    {
                                                        dataTablePresentTeachersTeacherIDandSlots.Rows[y].Delete();
                                                        dataTablePresentTeachersTeacherIDandSlots.AcceptChanges();
                                                    }

                                                }
                                                dataTablefoundsimilarslot.Rows.Clear();//clear all values in dt
                                                dataTableNo_OF_slots.Rows.Clear();
                                            }

                                        }

                                        break;//stop looping from here 


                                    }
                                    dataTableNo_OF_slots.Rows.Clear();//clear all values in "dataTableNo_OF_slots.Rows" dt

                                }

                            }
                            dataTablefirstAbsentTeacherRow.Rows.Clear();//clear all values in "dataTablefirstAbsentTeacherRow" dt

                        }
                    }
                }


                
                
                DateTime.Now.ToString("yyyy-MM-dd");
                string dateAsStringg = DateTime.Now.ToString("yyyy-MM-dd");
                
                DataTable assignedTeachers = new DataTable();
                assignedTeachers = mysub2.getSubstituteTeachers(dateAsStringg);

                //RadGrid2.PageSize = assignedTeachers.Rows.Count;
                RadGrid5.DataSource = assignedTeachers; //assigned teachers details are loaded to the rad grid
                RadGrid5.DataBind();


                mysub2.deletePresentfreeTeachersTable();

            }

            catch (Exception e1)
            {
                throw e1;
            }



        }


        protected void OKButn_Click(object sender, EventArgs e)
        {
            Response.Redirect("NotAllocatedTeachers.aspx");
        }

        

        protected void SendMailButn_Click(object sender, EventArgs e)
        {
            try{
                substituteDA mysub2 = new substituteDA();
                 SendMail();
                bool message = false;
                message=mysub2.getrecordsfromTable();
                if (message == true)
               {
                    //ClientScript.RegisterStartupScript(this.GetType(), "", "alert('Mails have sent to the all assigned teachers')", true);
                   //ClientScript.RegisterStartupScript(typeof(Page), "Mails have sent to the all assigned teachers", "script", true);
                   Response.Write("<script language=javascript>alert('Mails have successfully sent.')</script>");
               }
            }
        
           
           
            
           catch(Exception ex)
            {
               throw ex;
           }
        }

        public void SendMail()
        {
            bool message = false;
            substituteDA mysub = new substituteDA();

            DataTable assignedTeachers = new DataTable();
            DateTime.Now.ToString("yyyy-MM-dd");
            string dateAsString = DateTime.Now.ToString("yyyy-MM-dd");
            DataTable datatablereplaceTeacherDetails = new DataTable();
            assignedTeachers = mysub.getSubstituteTeachers(dateAsString);

            DataTable datatablereplacingteacherID = new DataTable();
            datatablereplacingteacherID.Columns.Add("Replacing Teacher ID");
            string replacing_teacher_id = "";

            for (int i = 0; i < assignedTeachers.Rows.Count; i++)
            {
                replacing_teacher_id = assignedTeachers.Rows[i][3].ToString();
                //datatablereplaceTeacherDetail = mysub.getReplaceTeacherDetails(replacing_teacher_id, dateAsString);
                object[] replacingTeacherID = { assignedTeachers.Rows[i][3].ToString() };
                datatablereplacingteacherID.Rows.Add(replacingTeacherID);

                //break;

            }

            //remove duplicate rows
            // create a dv from the source dt
            DataView dv = new DataView(datatablereplacingteacherID);
            // set the output columns array of the destination dt
            string[] strColumns = { "Replacing Teacher ID" };
            // true = yes, i need distinct values.
            datatablereplacingteacherID = dv.ToTable(true, strColumns);

            //for (int j = 0; j < assignedTeachers.Rows.Count; j++)
            //{
            DataTable checkrecordInDB = new DataTable();
           
            for (int a = 0; a < datatablereplacingteacherID.Rows.Count; a++)
            {
                string replaceTeacherID = datatablereplacingteacherID.Rows[a][0].ToString();
                checkrecordInDB = mysub.getRecordofDb(replaceTeacherID,dateAsString);
                int no_of_rows = checkrecordInDB.Rows.Count;
                if (no_of_rows == 0)
                {
                    for (int k = 0; k < datatablereplacingteacherID.Rows.Count; k++)
                    {
                        //replacing_teacher_id = datatablereplacingteacherID.Rows[a][0].ToString();
                        datatablereplaceTeacherDetails = mysub.getReplaceTeacherDetails(replaceTeacherID, dateAsString);
                       
                        var output = new StringBuilder();

                        var columnsWidths = new int[datatablereplaceTeacherDetails.Columns.Count];

                        // Get column widths
                        foreach (DataRow row in datatablereplaceTeacherDetails.Rows)
                        {
                            for (int i = 0; i < datatablereplaceTeacherDetails.Columns.Count; i++)
                            {
                                var length = row[i].ToString().Length;
                                if (columnsWidths[i] < length)
                                    columnsWidths[i] = length;
                            }
                        }

                        // Get Column Titles
                        for (int i = 0; i < datatablereplaceTeacherDetails.Columns.Count; i++)
                        {
                            var length = datatablereplaceTeacherDetails.Columns[i].ColumnName.Length;
                            if (columnsWidths[i] < length)
                                columnsWidths[i] = length;
                        }

                        // Write Column titles


                        for (int i = 0; i < datatablereplaceTeacherDetails.Columns.Count; i++)
                        {
                            var text = datatablereplaceTeacherDetails.Columns[i].ColumnName;
                            output.Append("|" + PadCenter(text, columnsWidths[i] + 2));
                        }
                        output.Append("|\n" + new string('.', output.Length) + "\n");

                        // Write Rows
                        foreach (DataRow row in datatablereplaceTeacherDetails.Rows)
                        {
                            for (int i = 0; i < datatablereplaceTeacherDetails.Columns.Count; i++)
                            {
                                var text = row[i].ToString();
                                output.Append("|" + PadCenter(text, columnsWidths[i] + 2));



                            }
                            output.Append("|\n");
                        }
                        output.ToString();

                       
       
                        for (int i = 0; i < datatablereplaceTeacherDetails.Rows.Count; i++)
                        {
                            string absentteacherID = datatablereplaceTeacherDetails.Rows[i][1].ToString();
                            string slotID = datatablereplaceTeacherDetails.Rows[i][2].ToString();
                            //DateTime.Now.ToString("yyyy-MM-dd");
                            //string dateAsString = DateTime.Now.ToString("yyyy-MM-dd");
                            string replacingteacherID = datatablereplaceTeacherDetails.Rows[i][4].ToString();
                           // for (int b = 0; b < datatablereplaceTeacherDetails.Rows.Count; b++)
                           // {
                               // string teacher_id = datatablereplaceTeacherDetails.Rows[i][].ToString();
                                DataTable dtmailID =mysub.getmailID(replacingteacherID);
                            string mailID = dtmailID.Rows[0][0].ToString();
                            //}

                                mysub.insertintoSubTeacherTable(absentteacherID, slotID, dateAsString, replacingteacherID, mailID);
                        }

                        mailsCLZ myMail = new mailsCLZ();
                        //bool message = false;
                        myMail.subject = "Substitute Teacher Allocation Details";
                        DataTable getreceivermail = new DataTable();
                        getreceivermail = mysub.getmail(replaceTeacherID);
                        myMail.receiverEmail = getreceivermail.Rows[0][0].ToString();

                        myMail.message = output.ToString();
                        message = myMail.SendMail(myMail);
                        //return message;
                        break;
                    }

                }
                else
                {
                    //ClientScript.RegisterStartupScript(this.GetType(), "", "alert('You already have sent mail')", true);
                }

            }

           
        }



        private static string PadCenter(string text, int maxLength)
        {
            int diff = maxLength - text.Length;
            return new string(' ', diff / 2) + text + new string(' ', (int)(diff / 2.0 + 0.5));

        } 
              
        
      

         
        }
    }
        




        
     
